package com.huaxia.dao.attend;

import com.huaxia.pojo.attend.SaleDeptLayerAttend;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/03 14:26
 */
public interface SaleDeptLayerAttendMapper {

    @Select("select a.saledeptsname,a.saledeptBcSignNum,b.saledeptBcNum,round(decode(b.saledeptBcNum,0,-9999,a.saledeptBcSignNum*100/b.saledeptBcNum),1) saledeptBcYield,c.saledeptBmSignNum,d.saledeptBmNum,round(decode(d.saledeptBmNum,0,-9999,c.saledeptBmSignNum*100/d.saledeptBmNum),1) saledeptBmYield from\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptBcSignNum from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num from\n" +
            "(select a.saledeptcode,a.agentcode from\n" +
            "(select t.saledeptcode,t.agentcode,t.agentgrade from D_AGENT_PROVINCE_YX2 t,\n" +
            "(select t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd')\n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)y where t.agentcode=y.staffcode and t.agentstate='在职')a where a.agentgrade in('SBC','BC'))a group by a.saledeptcode)a right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            ")a,\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptBcNum from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num from\n" +
            "(select t.saledeptcode,t.agentcode from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('SBC','BC','AS','SAS') and t.agentstate='在职'\n" +
            ")a group by a.saledeptcode)a right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            ")b,\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptBmSignNum from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num from\n" +
            "(select a.saledeptcode,a.agentcode from\n" +
            "(select t.saledeptcode,t.agentcode,t.agentgrade from D_AGENT_PROVINCE_YX2 t,\n" +
            "(select t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd')\n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)y where t.agentcode=y.staffcode and t.agentstate='在职')a where a.agentgrade in('SBM','BM'))a group by a.saledeptcode)a right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            ")c,\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptBmNum from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num from\n" +
            "(select t.saledeptcode,t.agentcode from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('SBM','BM','AS','SAS') and t.agentstate='在职'\n" +
            ")a group by a.saledeptcode)a right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            ")d\n" +
            "where a.saledeptsname=b.saledeptsname and b.saledeptsname=c.saledeptsname and c.saledeptsname=d.saledeptsname order by saleDeptBcYield DESC")
    List<SaleDeptLayerAttend> getSaleDeptLayerAttend();
}
